﻿Imports System.Data.SqlClient

Public Class TuVanDAO
    Public Shared Function ChonPin1(TongCongSuatPin As Integer) As DataTable
        Dim cn As SqlConnection = DataProvider.Connection()
        Dim dt As New DataTable()
        Dim sql As String = "select " + TongCongSuatPin.ToString() + "/(CongSuat) + (" + TongCongSuatPin.ToString() + "%(CongSuat))as SapXep," + TongCongSuatPin.ToString() + "/(CongSuat) as SoTamPin,(" + TongCongSuatPin.ToString() + "%(CongSuat)) as SoDu,MaPin,TenPin,CongSuat,Isc,pn.MaLoai,DonGia from PIN pn, LOAIPIN lp where pn.MaLoai = lp.MaLoai and " + TongCongSuatPin.ToString() + "/(CongSuat)<(select MAX(SoCongPin) from MACHDIEUKHIEN) ORDER BY SoDu desc"

        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt
    End Function

    Public Shared Function ChonNhieuHeThong(TongCongSuatPin As Integer) As DataTable
        Dim cn As SqlConnection = DataProvider.Connection()
        Dim dt As New DataTable()
        Dim sql As String = "select " + TongCongSuatPin.ToString() + "/(CongSuat) + (" + TongCongSuatPin.ToString() + "%(CongSuat))as SapXep," + TongCongSuatPin.ToString() + "/(CongSuat) as SoTamPin,(" + TongCongSuatPin.ToString() + "%(CongSuat)) as SoDu,MaPin,TenPin,CongSuat,Isc,pn.MaLoai from PIN pn, LOAIPIN lp where pn.MaLoai = lp.MaLoai and " + TongCongSuatPin.ToString() + "/(CongSuat)<(select MAX(SoCongPin) from MACHDIEUKHIEN) ORDER BY SoDu desc"

        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt
    End Function

    Public Shared Function ChonMachDieuKhien(SoTamPin As Integer, Isc As Double) As DataTable
        Dim cn As SqlConnection = DataProvider.Connection()
        Dim dt As New DataTable()
        Dim sql As String = "select * from MACHDIEUKHIEN where SoCongPin >= " + SoTamPin.ToString() + " and DongDien > (" + SoTamPin.ToString() + "*" + Isc.ToString() + ") and DongDien <= All(select DongDien from MACHDIEUKHIEN where SoCongPin >= " + SoTamPin.ToString() + " and DongDien > (" + SoTamPin.ToString() + "*" + Isc.ToString() + ") )"

        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt

    End Function

    Public Shared Function ChonAcquy(TongCongSuatPin As Integer, SoNgaySac As Integer, SoNgaySuDung As Integer) As DataTable
        Dim cn As SqlConnection = DataProvider.Connection()
        Dim dt As New DataTable()
        Dim sql As String = "select Convert(int,(((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ")/DongDien) + ( (((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ") - Convert(int,(((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ")/DongDien)*DongDien) as SapXep,Convert(int,(((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ")/DongDien) as SoBinh, ( (((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ") - Convert(int,(((" + TongCongSuatPin.ToString() + "*" + SoNgaySuDung.ToString() + ")/2) * " + SoNgaySac.ToString() + ")/DongDien)*DongDien) as SoDu, * from ACQUY order by SapXep asc"

        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt
    End Function

    Public Shared Function ChonBoKichDien(CoThietBiDeBa As Boolean, TongCongSuatTai As Integer, DienApTai As Integer) As DataTable
        Dim cn As SqlConnection = DataProvider.Connection()
        Dim dt As New DataTable()
        Dim sql As String
        If CoThietBiDeBa = True Then
            sql = "select * from BOKICHDIEN where MaLoai = 2 and CongSuatTai > " + TongCongSuatTai.ToString() + " and CongSuatTai <= All(select CongSuatTai from BOKICHDIEN where MaLoai = 2 and CongSuatTai > " + TongCongSuatTai.ToString() + ") "
        Else
            sql = "select * from BOKICHDIEN where CongSuatTai > " + TongCongSuatTai.ToString() + " and CongSuatTai <= All(select CongSuatTai from BOKICHDIEN where CongSuatTai > " + TongCongSuatTai.ToString() + " )"

        End If
        
        Dim da As New SqlDataAdapter(sql, cn)
        da.Fill(dt)
        Return dt
    End Function
End Class
